
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE id = object_id(N'[dbo].[GetCOIReOpened]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GetCOIReOpened]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************  
* Name:   GetCOIReOpened
* Purpose:  Retruns 1 if the COI is Re-Opened else 0
*  
* PARAMETERS  
* Name         Description       
* -------------      -------------------------------------------  
* @coiId     COI Id  
******************************************************************/  
  
CREATE Procedure [dbo].[GetCOIReOpened]  
(
	@COIId INT
)
AS  
BEGIN  
  
SET NOCOUNT ON  
	
	DECLARE @ActionDate DATETIME
	--DECLARE @Count INT
	
	--Getting the Last submission Date.	  
	SELECT TOP 1 @ActionDate = CA.ActionDate FROM ConflictOfInterest COI
		INNER JOIN COIAudit CA ON CA.COIId = COI.Id
	WHERE 
	COIId = @COIId
	AND
	CA.[Action] = 'COI Submitted' OR CA.[Action] = 'COI ReSubmitted'
	ORDER BY CA.Id DESC
	
	--If null then COI is never submitted and hence it't not re-opened.
	IF(ISNULL(@ActionDate,0) = 'Jan  1 1900 12:00AM')
	BEGIN
		SELECT 0 AS COIReOpened 
		RETURN
	END
	
	--Getting the count of records 
	SELECT COUNT(*) FROM ConflictOfInterest COI
		INNER JOIN COIAudit CA ON CA.COIId = COI.Id
	WHERE 
	COIId = @COIId
	AND
	CA.ActionDate > @ActionDate
	AND
	CA.[Action] = 'Reset COI to InProgress'
	
	--SELECT @Count AS COIReOpened

END  

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  

  

  
--EXEC GetCOIReOpened @COIId=16612 